-- @owner: @haomeng802
-- @date: 2023/1/3
-- @testpoint: list分区表结合connect by子句
-- @modified by @haomeng802 at 2023/1/31:优化用例解决排序问题

--step1:创建list分区表，插入数据  expect:建表成功，插入数据成功
drop table if exists t_connectby_006 cascade;
create table t_connectby_006(e_id int,e_name varchar(10),l_id int)
partition by list(e_id)
(partition p1 values (10),
 partition p2 values (20),
 partition p3 values (30),
 partition p4 values (40),
 partition p5 values (50));

insert into t_connectby_006 values(30,'arise',10);
insert into t_connectby_006 values(20,'jack',20);
insert into t_connectby_006 values(50,'judy',20);
insert into t_connectby_006 values(10,'lion',30);
insert into t_connectby_006 values(40,'scott',20);
insert into t_connectby_006 values(30,'tiger',30);
insert into t_connectby_006 values(10,'king',10);


--step2:查询以l_id为10开始的节点的直属节点  expect:成功
select * from t_connectby_006 start with l_id = 10 connect by nocycle prior l_id = e_id order by l_id;

--step3:结合层次递归查询函数查看递归层级  expect:成功
select sys_connect_by_path(e_name,'-') as path, *, level from t_connectby_006 start with l_id = 10 connect by nocycle prior l_id = e_id;

--step4:清理环境   expect:成功
drop table t_connectby_006 cascade;
